

/**
 * users
 */
create table users (
	user_id bigserial primary key,
	password text not null,
	nick_name text,
	sex integer,
	head_icon text,
	birthday date,
	balance float default 0.0,
	type integer not null,
	creation_time timestamp,
	status integer not null,
	invitation_code text,
	address text,
	activity_status integer DEFAULT 0, /*0 未参与活动 邀请两个好友免费购买一次产品的机会 10已参与*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table users_history (
	change_sequence bigserial primary key,
	user_id bigint,
	password text,
	nick_name text,
	sex integer,
	head_icon text,
	birthday date,
	balance float,
	user_project_financing float,
	type integer,
	creation_time timestamp,
	status integer,
	address text,
	activity_status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index users_history_user_id on users_history using BTREE (user_id);

create or replace function archive_users() returns trigger as $archive_users$
begin
		if (new.version = old.version + 1) then
			insert into users_history (
					user_id,
					password,
					nick_name,
					sex,
					head_icon,
					birthday,
					balance,
					type,
					creation_time,
					status,
					address,
					activity_status,
					proc_name,
					proc_channel,
					proc_ts,
					proc_user_id,
					session_id,
					version
			) values (
					old.user_id,
					old.password,
					old.nick_name,
					old.sex,
					old.head_icon,
					old.birthday,
					old.balance,
					old.type,
					old.creation_time,
					old.status,
					old.address,
					old.activity_status,
					old.proc_name,
					old.proc_channel,
					old.proc_ts,
					old.proc_user_id,
					old.session_id,
					old.version
			);
			return new;
		else
			raise exception 'update locked.';
			end if;
end;
$archive_users$ language plpgsql;
create trigger archive_users before update on users for each row execute procedure archive_users();




/**
 * model
 * 活动 例如：0元认购，热门商品等
 */
create table model (
	model_id bigserial primary key,
	model_url text,
	model_type integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index model_model_type on model using BTREE (model_type);

create table model_history (
	sequence bigserial primary key,
	model_id bigint,
	model_url text,
	model_type integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index model_history_model_type on model using BTREE (model_type);

create or replace function archive_model() returns trigger as $archive_model$
begin
	if (new.version = old.version + 1) then
		insert into model_history (
			model_id,
			model_url,
			model_type,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.model_id,
			old.model_url,
			old.model_type,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_model$ language plpgsql;
create trigger archive_model before update on model for each row execute procedure archive_model();


/**
 * invitation
 */
create table invitation (
	inviter_id bigint references users on update cascade on delete restrict, /*邀请者*/
	invitee_id bigint references users on update cascade on delete restrict,  /*被邀请者*/
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	primary key(inviter_id,invitee_id)
);

create table invitation_history (
	sequence bigserial primary key,
	inviter_id bigint, /*邀请者*/
	invitee_id bigint,  /*被邀请者*/
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index invitation_history_inviter_id on invitation_history  using BTREE (inviter_id);
create index invitation_history_invitee_id on invitation_history  using BTREE (invitee_id);

create or replace function archive_invitation() returns trigger as $archive_invitation$
begin
	if (new.version = old.version + 1) then
		insert into invitation_history (
			inviter_id,
			invitee_id,
			status,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.inviter_id,
			old.invitee_id,
			old.status,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_invitation$ language plpgsql;
create trigger archive_invitation before update on invitation for each row execute procedure archive_invitation();


/**
 * user_aliases
 */
create table user_aliases (
	user_id bigint references users on update cascade on delete restrict,
	type integer not null,
	alias_name text not null,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	primary key(user_id, type)
);
create index user_aliases_alias_name on user_aliases  using BTREE (alias_name);

create table user_aliases_history (
	change_sequence bigserial primary key,
	user_id bigint,
	type integer,
	alias_name text,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create or replace function archive_user_aliases() returns trigger as $archive_user_aliases$
begin
	if (new.version = old.version + 1) then
		insert into user_aliases_history (
			user_id,
			type,
			alias_name,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.user_id,
			old.type,
			old.alias_name,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_user_aliases$ language plpgsql;
create trigger archive_user_aliases before update on user_aliases for each row execute procedure archive_user_aliases();


/**
 * project
 */
create table projects (
	project_id bigserial primary key,
	user_id bigint not null references users on update cascade on delete restrict,
	title text,
	advantage text,
	cover text,
	name text,
	type integer not null,
	financing integer not null,
	max_people integer,
	min_people integer,
	overflow integer not null,
	end_time date,
	tags integer not null,
	start_money integer,
	every_max_buy integer,
	publish_time timestamp,
	status integer not null,
	province text not null,
	city text not null,
	area text not null,
	model_id bigint default 19023 references model on update cascade on delete restrict, /*活动 如0元购买，热门推荐等*/
	dispatch_time timestamp,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index project_name on projects using BTREE (name);

create table projects_history (
	change_sequence bigserial primary key,
	project_id bigint,
	user_id bigint,
	title text,
	advantage text,
	cover text,
	name text,
	type integer,
	financing integer,
	max_people integer,
	min_people integer,
	overflow integer,
	end_time date,
	tags integer,
	start_money integer,
	every_max_buy integer,
	publish_time timestamp,
	status integer,
	province text,
	city text,
	area text,
	model_id bigint,
	dispatch_time timestamp,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
	);
create index projects_history_project_id on projects_history using BTREE (project_id);
create index projects_history_user_id on projects_history using BTREE (user_id);

create or replace function archive_project() returns trigger as $archive_project$
begin
	if (new.version = old.version + 1) then
		insert into projects_history (
			project_id,
			user_id,
			title,
			advantage,
			cover,
			name,
			type,
			financing,
			max_people,
			min_people,
			overflow,
			end_time,
			tags,
			start_money,
			every_max_buy,
			publish_time,
			status,
			province,
			city,
			area,
			model_id,
			dispatch_time,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.project_id,
			old.user_id,
			old.title,
			old.advantage,
			old.cover,
			old.name,
			old.type,
			old.financing,
			old.max_people,
			old.min_people,
			old.overflow,
			old.end_time,
			old.tags,
			old.start_money,
			old.every_max_buy,
			old.publish_time,
			old.status,
			old.province,
			old.city,
			old.area,
			old.model_id,
			old.dispatch_time,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_project$ language plpgsql;
create trigger archive_project before update on projects for each row execute procedure archive_project();

/**
 * audit
 */
create table audit (
	audit_id bigserial primary key,
	user_id bigint references users on update cascade on delete restrict,
	project_id bigint UNIQUE references projects on update cascade on delete restrict,
	audit_number integer,
	audit_name text,
	audit_photo text,
	create_time timestamp,
	status integer DEFAULT 10,
	reason text,
	type integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index audit_audit_number on audit using BTREE (audit_number);
create index audit_audit_name on audit using BTREE (audit_name);

create table audit_history (
	sequence bigserial primary key,
	audit_id bigint,
	user_id bigint,
	project_id bigint,
	audit_number integer,
	audit_name text,
	audit_photo text,
	create_time timestamp,
	status integer,
	reason text,
	type integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index audit_history_user_id on audit_history using BTREE (user_id);
create index audit_history_project_id on audit_history using BTREE (project_id);

create or replace function archive_audit() returns trigger as $archive_audit$
begin
	if (new.version = old.version + 1) then
		insert into audit_history (
			audit_id,
			user_id,
			project_id,
			audit_number,
			audit_name,
			audit_photo,
			create_time,
			status,
			reason,
			type,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.audit_id,
			old.user_id,
			old.project_id,
			old.audit_number,
			old.audit_name,
			old.audit_photo,
			old.create_time,
			old.status,
			old.reason,
			old.type,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_audit$ language plpgsql;
create trigger archive_audit before update on audit for each row execute procedure archive_audit();

/**
 * addAgreement
 */
create table agreement (
	agreement_id bigserial primary key,
	start_time timestamp not null,
	end_time timestamp not null,
	user_id bigint references users on update cascade on delete restrict,
	project_id bigint references projects on update cascade on delete restrict not null,
	costing float,
	settle_ment float,
	service float,
	sale float,
	bonus float,/*分红百分比*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table agreement_history (
	sequence bigserial primary key,
	agreement_id bigint,
	start_time timestamp,
	end_time timestamp,
	user_id bigint,
	project_id bigint,
	costing float,
	settle_ment float,
	service float,
	sale float,
	bonus float,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index agreement_history_agreement_id on agreement_history using BTREE (agreement_id);
create index agreement_history_user_id on agreement_history using BTREE (user_id);
create index agreement_history_project_id on agreement_history using BTREE (project_id);

create or replace function archive_agreement() returns trigger as $archive_agreement$
begin
	if (new.version = old.version + 1) then
		insert into agreement_history (
			agreement_id,
			start_time,
			end_time,
			user_id,
			project_id,
			costing,
			settle_ment,
			service,
			sale,
			bonus,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.agreement_id,
			old.start_time,
			old.end_time,
			old.user_id,
			old.project_id,
			old.costing,
			old.settle_ment,
			old.service,
			old.sale,
			old.bonus,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_agreement$ language plpgsql;
create trigger archive_agreement before update on agreement for each row execute procedure archive_agreement();


/**
 * project_introduce
 */
create table project_introduce (
	project_introduce_id bigserial primary key,
	project_id bigint references projects on update cascade on delete restrict not null,
	type integer not null,
	content text,
	c_type integer not null,
	tum integer, /*该内容在介绍中占第几个*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table project_introduce_history (
	sequence bigserial primary key,
	project_id bigint,
	type integer,
	content text,
	c_type integer,
	tum integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index project_introduce_history_project_id on project_introduce_history using BTREE (project_id);

create or replace function archive_project_introduce() returns trigger as $archive_project_introduce$
begin
	if (new.version = old.version + 1) then
		insert into project_introduce_history (
			project_id,
			type,
			content,
			c_type,
			tum,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.project_id,
			old.type,
			old.content,
			old.c_type,
			old.tum,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_project_introduce$ language plpgsql;
create trigger archive_project_introduce before update on project_introduce for each row execute procedure archive_project_introduce();

/**
 * skus
 */
create table skus (
	sku_id bigserial primary key,
	project_id bigint references projects on update cascade on delete restrict not null,
	number integer,
	name text not null,
	unitprice float not null,
	earnings text not null,
	profit float,
	sku_url text,
	real_price float,
	dispatch_price float,
	max_people integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	constraint fk_skus_project_id foreign key (project_id) references projects (project_id) on update cascade on delete restrict
);
create index skus_name on skus using BTREE (name);

create table skus_history (
	sequence bigserial primary key,
	sku_id bigint,
	project_id bigint,
	number integer,
	name text,
	unitprice float,
	earnings text,
	profit float,
	sku_url text,
	real_price float,
	dispatch_price float,
	max_people integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index skus_history_sku_id on skus_history using BTREE (sku_id);
create index skus_history_name on skus_history using BTREE (name);
create index skus_history_project_id on skus_history using BTREE (project_id);

create or replace function archive_skus() returns trigger as $archive_skus$
begin
	if (new.version = old.version + 1) then
		insert into skus_history (
			sku_id,
			project_id,
			number,
			name,
			unitprice,
			earnings,
			profit,
			sku_url,
			real_price,
			dispatch_price,
			max_people,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.sku_id,
			old.project_id,
			old.number,
			old.name,
			old.unitprice,
			old.earnings,
			old.profit,
			old.sku_url,
			old.real_price,
			old.dispatch_price,
			old.max_people,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_skus$ language plpgsql;
create trigger archive_skus before update on skus for each row execute procedure archive_skus();

/**
 * project_income
 */
create table project_income (
	project_income_id bigserial primary key,
	project_id bigint references projects on update cascade on delete restrict not null,
	photo text,
	name text not null,
	money float not null,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table project_income_history (
	sequence bigserial primary key,
	project_id bigint,
	photo text,
	name text,
	money float,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index project_income_project_id on project_income_history using BTREE (project_id);

create or replace function archive_project_income() returns trigger as $archive_project_income$
begin
	if (new.version = old.version + 1) then
		insert into project_income_history (
			project_id,
			photo,
			name,
			money,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.project_id,
			old.photo,
			old.name,
			old.money,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_project_income$ language plpgsql;
create trigger archive_project_income before update on project_income for each row execute procedure archive_project_income();


/**
 * activity
 */
create table activity (
	activity_id bigserial primary key,
	acti_time timestamp not null,
	name text not null,
	address text not null,
	user_id bigint references users on update cascade on delete restrict,
	status integer not null,
	activity_description text,
	cover text,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table activity_history (
	sequence serial primary key,
	activity_id bigint,
	acti_time timestamp,
	name text,
	address text,
	user_id bigint,
	status integer,
	activity_description text,
	cover text,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index activity_history_activity_id on activity_history using BTREE (activity_id);
create index activity_history_user_id on activity_history using BTREE (user_id);


create or replace function archive_activity() returns trigger as $archive_activity$
begin
	if (new.version = old.version + 1) then
		insert into activity_history (
			activity_id,
			acti_time,
			name,
			address,
			user_id,
			status,
			activity_description,
			cover,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.activity_id,
			old.acti_time,
			old.name,
			old.address,
			old.user_id,
			old.status,
			old.activity_description,
			old.cover,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_activity$ language plpgsql;
create trigger archive_activity before update on activity for each row execute procedure archive_activity();

/**
 * user_activity
 */
create table user_activity (
	user_id bigint references users on update cascade on delete restrict,
	activity_id bigint references activity on update cascade on delete restrict,
	status Integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	primary key(user_id, activity_id)
);

create table user_activity_history(
	sequence serial primary key,
	user_id bigint,
	activity_id bigint,
	status Integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index user_activity_history_user_id on user_activity_history using BTREE (user_id);
create index user_activity_history_activity_id on user_activity_history using BTREE (activity_id);

create or replace function archive_user_activity() returns trigger as $archive_user_activity$
begin
	if (new.version = old.version + 1) then
		insert into user_activity_history (
			user_id,
			activity_id,
			status,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.user_id,
			old.activity_id,
			old.status,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_user_activity$ language plpgsql;
create trigger archive_user_activity before update on user_activity for each row execute procedure archive_user_activity();


/**
 * orders
 */
------------
-- orders --
------------
create table orders (
	order_id bigserial primary key,
	user_id bigint references users on update cascade on delete restrict not null,
	name text, /* 收货人姓名 */
	phone_number text, /*收货人电话*/
	province text,
	city text,
	address text,
	use_comments text,
	service_comments text,
	status integer not null,
	serial_number text,  /*采用时间+无序列字母两个+用户ID 暂定*/
	express_company text,/*快递公司名*/
	express_number text,			/*快递单号*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table orders_history (
	sequence serial primary key,
	order_id bigint,
	user_id bigint,
	name text,
	phone_number text,
	province text,
	city text,
	address text,
	use_comments text,
	service_comments text,
	status integer not null,
	serial_number text,  /*采用时间+无序列字母两个+用户ID 暂定*/
	express_company text,/*快递公司名*/
	express_number text,			/*快递单号*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index orders_history_order_id on orders_history using BTREE (order_id);
create index orders_history_user_id on orders_history using BTREE (user_id);

create or replace function archive_orders() returns trigger as $archive_orders$
begin
	if (new.version = old.version + 1) then
		insert into orders_history (
			order_id,
			user_id,
			name,
			phone_number,
			province,
			city,
			address,
			use_comments,
			service_comments,
			status,
			serial_number,
			express_company,
			express_number,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.order_id,
			old.user_id,
			old.name,
			old.phone_number,
			old.province,
			old.city,
			old.address,
			old.use_comments,
			old.service_comments,
			old.status,
			old.serial_number,
			old.express_company,
			old.express_number,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_orders$ language plpgsql;
create trigger archive_orders before update on orders for each row execute procedure archive_orders();

/**
 * orders_item
 */
create table order_item (
	order_id bigint references orders on update cascade on delete restrict,
	sku_id bigint references skus on update cascade on delete restrict,
	units integer not null,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	primary key(order_id, sku_id)
);

create table order_item_history (
	sequence bigserial primary key,
	order_id bigint,
	sku_id bigint,
	units integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index order_item_history_order_id on order_item_history using BTREE (order_id);
create index order_item_history_sku_id on order_item_history using BTREE (sku_id);

create or replace function archive_order_item() returns trigger as $archive_order_item$
begin
	if (new.version = old.version + 1) then
		insert into order_item_history (
			order_id,
			sku_id,
			units,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.order_id,
			old.sku_id,
			old.units,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_order_item$ language plpgsql;
create trigger archive_order_item before update on order_item for each row execute procedure archive_order_item();

/**
 * bill
 */
create table bill (
	bill_id bigserial primary key,
	user_id bigint references users on update cascade on delete restrict,
	money float,
	target_account text,
	status integer, /*成功 or 失败*/
	type integer not null, /*转入金额还是转出*/
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create table bill_history (
	sequence bigserial primary key,
	user_id bigint,
	money float,
	target_account text,
	status integer,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);
create index bill_history_user_id on bill_history using BTREE (user_id);

create or replace function archive_bill() returns trigger as $archive_bill$
begin
	if (new.version = old.version + 1) then
		insert into bill_history (
			user_id,
			money,
			target_account,
			status,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.user_id,
			old.money,
			old.target_account,
			old.status,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_bill$ language plpgsql;
create trigger archive_bill before update on bill for each row execute procedure archive_bill();

/**
 * project_back
 * 联合产品的衍生产品
 */
create table project_back (
	main_project_id bigint references projects on update cascade on delete restrict,
	slave_project_id bigint references projects on update cascade on delete restrict,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer,
	primary key(main_project_id, slave_project_id)
);

create table project_back_history (
	sequence bigserial primary key,
	main_project_id bigint,
	slave_project_id bigint,
	proc_name text,
	proc_channel text,
	proc_ts timestamp,
	proc_user_id bigint,
	session_id text,
	version integer
);

create or replace function archive_project_back() returns trigger as $archive_project_back$
begin
	if (new.version = old.version + 1) then
		insert into project_back_history (
			main_project_id,
			slave_project_id,
			proc_name,
			proc_channel,
			proc_ts,
			proc_user_id,
			session_id,
			version
		) values (
			old.main_project_id,
			old.slave_project_id,
			old.proc_name,
			old.proc_channel,
			old.proc_ts,
			old.proc_user_id,
			old.session_id,
			old.version
		);
		return new;
	else
		raise exception 'update locked.';
		end if;
end;
$archive_project_back$ language plpgsql;
create trigger archive_project_back before update on project_back for each row execute procedure archive_project_back();
